﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using YY.UI.BaseDataSvc;
using YY.Common;
using System.Collections;
using Yogesh.ExcelXml;
using System.Configuration;

namespace YY.UI
{
    public partial class BillFrm : BaseFrm
    {
        EnumCommon.FeeType _FeeType = EnumCommon.FeeType.应收;

        IEnumerable<TB_Clients> clients = null;

        /// <summary>
        /// 通用分类数据临时变量
        /// </summary>
        IEnumerable<TB_Options> options = null;

        public BillFrm()
        {
            InitializeComponent();
            this.BaseDataProxy = new YY.UI.BaseDataSvc.BaseDataClient();
        }

        private void BillFrm_Load(object sender, EventArgs e)
        {
            this.dataGridView1.AutoGenerateColumns = false;
            this.dataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.EnableResizing;//AutoSize十分影响性能
            this.dataGridView1.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;
            this.dataGridView1.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders;
            this.dtpEDate.Value = DateTime.Now;
            this.dtpSDate.Value = DateTime.Now.AddMonths(-1);

            this.clients = this.BaseDataProxy.FindClients(null, null, null, null, null);
            this.options = this.BaseDataProxy.FindOptions(EnumCommon.OptionType.结算对象.ToString(), null, null);

            bindTarget();

        }

        private void bindTarget()
        {
            if (this.rdBtnYS.Checked)
            {
                this._FeeType = EnumCommon.FeeType.应收;
                Common.ComboBoxBindDatas<TB_Clients>(this.cbBoxTarget, this.clients, "fullName", "id");
                this.cbBoxTarget.KeyPress -= new KeyPressEventHandler(optionCbBox_KeyPress);
                this.cbBoxTarget.KeyPress += new KeyPressEventHandler(cbBoxClient_KeyPress);
            }
            else
            {
                this._FeeType = EnumCommon.FeeType.应付;
                Common.BindOptionsComboBox(this.cbBoxTarget, this.options, EnumCommon.OptionType.结算对象);
                this.cbBoxTarget.KeyPress -= new KeyPressEventHandler(cbBoxClient_KeyPress);
                this.cbBoxTarget.KeyPress += new KeyPressEventHandler(optionCbBox_KeyPress);
            }
        }

        //初始化数据列
        private void bindDgvColumns(string[] excepts)
        {
            if (this.dt != null)
            {
                this.dataGridView1.Columns.Clear();
                DataGridViewCheckBoxColumn colCB = new DataGridViewCheckBoxColumn();
                DataGridViewCheckBoxHeaderCell cbHeader = new DataGridViewCheckBoxHeaderCell();
                colCB.HeaderCell = cbHeader;
                Common.AddDgvColumn(this.dataGridView1, colCB, "check", "", true, false, 55);
                //增加了一个字段是"否有发票"，不显示在DataGridView里，所以减1
                for (int i = 0; i < dt.Columns.Count-1; i++)
                {
                    bool display = !excepts.Contains(dt.Columns[i].ColumnName);
                    Common.AddDgvColumn(this.dataGridView1, new DataGridViewTextBoxColumn(), dt.Columns[i].ColumnName, dt.Columns[i].ColumnName, display, true, null);
                }
                cbHeader.OnCheckBoxClicked -= new CheckBoxClickedHandler(cbHeader_OnCheckBoxClicked);
                cbHeader.OnCheckBoxClicked += new CheckBoxClickedHandler(cbHeader_OnCheckBoxClicked);
                //this.dataGridView1.Columns[9].DefaultCellStyle.Format = dateFormat
            }
        }

        void cbHeader_OnCheckBoxClicked(bool isChecked)
        {
            foreach (DataGridViewRow row in this.dataGridView1.Rows)
                row.Cells[0].Value = isChecked;
            this.dataGridView1.EndEdit();
        }

        private void cbBoxClient_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Enter)
            {
                ComboBox cbBox = sender as ComboBox;
                if (this.clients != null)
                {
                    TB_Clients[] datas = this.clients.Where(p => p.fullName.Contains(cbBox.Text) || p.shortName.Contains(cbBox.Text)).ToArray();
                    Common.ComboBoxMatchClient(cbBox, datas, this);
                }
            }
        }

        /// <summary>
        /// 通用分类选择框回车事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void optionCbBox_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == (char)Keys.Enter)
            {
                ComboBox cbBox = sender as ComboBox;
                EnumCommon.OptionType ot = (EnumCommon.OptionType)cbBox.Tag;
                if (this.options != null)
                {
                    TB_Options[] os = this.options.Where(p => p.type == ot.ToString()).Where(p => p.name.Contains(cbBox.Text) || p.code.Contains(cbBox.Text)).ToArray();
                    Common.ComboBoxMatchOption(cbBox, os, this, ot);
                }
            }
        }

        DataTable dt = null;
        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (this.cbBoxTarget.SelectedItem == null)
            {
                MessageBox.Show("请选择结算对象！");
                this.cbBoxTarget.Focus();
                return;
            }

            DateTime dtStart = this.dtpSDate.Value.Date;
            DateTime dtEnd = this.dtpEDate.Value.AddDays(1).Date.AddSeconds(-1);


            int id = 0;
            if (this._FeeType == EnumCommon.FeeType.应收)
                id = (this.cbBoxTarget.SelectedItem as TB_Clients).id;
            else
                id = (this.cbBoxTarget.SelectedItem as TB_Options).id;

            this.dt = this.BaseDataProxy.GetOrderBill(id, dtStart, dtEnd, (int)this._FeeType);
            if (dt != null && dt.Rows.Count > 0)
            {
                //dt.Columns.Add(new DataColumn("箱型", typeof(string)));
                //dt.Columns.Add(new DataColumn("箱号", typeof(string)));
                //dt.Columns.Add(new DataColumn("封号", typeof(string)));
                //dt.Columns.Add(new DataColumn("check", typeof(bool)));

                IEnumerable<TB_Options> os = this.BaseDataProxy.FindOptions(EnumCommon.OptionType.费目类型.ToString(), null, null);

                foreach (DataRow dr in dt.Rows)
                {
                    int orderId = Convert.ToInt32(dr["orderId"]);
                    TB_Case[] cases = this.BaseDataProxy.GetCases(orderId);
                    if (cases != null)
                    {
                        var models = from m in cases select m.model;
                        var codes = from m in cases select m.code;
                        var titles = from m in cases select m.title;
                        dr["箱型"] += string.Join("\n", models.ToArray<string>());
                        dr["箱号"] += string.Join("\n", codes.ToArray<string>());
                        dr["封号"] += string.Join("\n", titles.ToArray<string>());
                    }

                    IEnumerable<TB_Fee> fees = this.BaseDataProxy.GetFees(orderId);

                    decimal total = 0;
                    if (fees != null)
                    {
                        fees = fees.Where(p => p.feeType == (int)this._FeeType && p.isVerify);

                        if (EnumCommon.FeeType.应付 == this._FeeType)
                            fees = fees.Where(p => p.target == id);

                        var q = from f in fees.Where(p => p.feeType == (int)this._FeeType && p.isVerify)
                                join o in os on f.item equals o.id
                                select (o.name + "：" + f.amount.ToString());

                        dr["费用"] += string.Join("\n", q.ToArray<string>());
                        total = fees.Sum(s => s.amount);
                        dr["总额"] = total;
                    }
                }
                dt.AcceptChanges();
                bindDgvColumns(new string[] { "id", "id1", "orderId" });
            }
            this.dataGridView1.DataSource = dt;
        }

        private void printBill()
        {
            //输出Excel
            ExcelCommon excel = new ExcelCommon();
            try
            {
                if (dt != null)
                {
                    //if (dt.Columns.Contains("check"))
                    //    dt.Columns.Remove(dt.Columns["check"]);
                    if (dt.Columns.Contains("id"))
                        dt.Columns.Remove(dt.Columns["id"]);
                    if (dt.Columns.Contains("id1"))
                        dt.Columns.Remove(dt.Columns["id1"]);
                    if (dt.Columns.Contains("orderId"))
                        dt.Columns.Remove(dt.Columns["orderId"]);
 
                    dt.AcceptChanges();

                    bool hasInvince = dt.Rows[0]["hasInvoice"].ToString() == "True";
                    TB_Clients client = null;
                    if (hasInvince)
                    {
                        int id = 0;
                        if (this._FeeType == EnumCommon.FeeType.应收)
                            id = (this.cbBoxTarget.SelectedItem as TB_Clients).id;
                        else
                            id = (this.cbBoxTarget.SelectedItem as TB_Options).id;
                        client = BaseDataProxy.GetClient(id);
                    }
                    if (dt.Columns.Contains("hasInvoice"))
                        dt.Columns.Remove(dt.Columns["hasInvoice"]);

                    excel.Create();

                    Microsoft.Office.Interop.Excel.Worksheet ws2 = excel.AddSheet("账号");
                    excel.SetCellValue(ws2, 1, 1, "广州市科洋货运代理有限公司");
                    excel.SetCellProperty(ws2, 1, 1, 1, 1, 26, false, "隶书", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 2, 1, "Guangzhou Keyang Transportation Agency Co.,Ltd.");
                    excel.SetCellProperty(ws2, 2, 1, 2, 1, 14, true, "华文新魏", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 3, 1, "电话Tel: 020-28945185，传真Fax:020-89718757");
                    excel.SetCellProperty(ws2, 3, 1, 3, 1, 9, false, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 4, 1, "地址Add：广州市黄埔区黄埔东路5号东盛国际1616室");
                    excel.SetCellProperty(ws2, 4, 1, 4, 1, 9, false, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 5, 1, "");
                    excel.SetCellValue(ws2, 6, 1, "");
                    excel.SetCellValue(ws2, 7, 1, "开户行:中国工商银行-黄埔大道西支行");
                    excel.SetCellProperty(ws2, 7, 1, 7, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 8, 1, "卡号:6222 0236 0204 0740 607");
                    excel.SetCellProperty(ws2, 8, 1, 8, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 9, 1, "用户名:黄绍官");
                    excel.SetCellProperty(ws2, 9, 1, 9, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 10, 1, "");
                    excel.SetCellValue(ws2, 11, 1, "开户行:中国银行-广州港湾路支行");
                    excel.SetCellProperty(ws2, 11, 1, 11, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 12, 1, "卡号:6216 6119 0000 1711 702");
                    excel.SetCellProperty(ws2, 12, 1, 12, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 13, 1, "用户名:黄绍官");
                    excel.SetCellProperty(ws2, 13, 1, 13, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 14, 1, "");
                    excel.SetCellValue(ws2, 15, 1, "开户行:中国农业银行-黄埔支行");
                    excel.SetCellProperty(ws2, 15, 1, 15, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 16, 1, "卡号:6228 4800 8410 1257 115");
                    excel.SetCellProperty(ws2, 16, 1, 16, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.SetCellValue(ws2, 17, 1, "用户名:黄绍官");
                    excel.SetCellProperty(ws2, 17, 1, 17, 1, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    excel.ColumnsAutoFit(ws2, 1, 1, 17, 1);

                    //IEnumerable<TB_User> users = this.BaseDataProxy.FindUsers(null, null);
                    excel.ws = excel.AddSheet("账单");

                    excel.SetCellValue(excel.ws, 1, 1, "广州市科洋货运代理有限公司");///row1
                    excel.UniteCells(excel.ws, 1, 1, 1, dt.Columns.Count);
                    excel.SetCellProperty(excel.ws, 1, 1, 1, dt.Columns.Count, 30, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                    excel.SetCellValue(excel.ws, 2, 1, "Guangzhou Keyang Transportation Agency Co.Ltd.");//row2
                    excel.UniteCells(excel.ws, 2, 1, 2, dt.Columns.Count);
                    excel.SetCellProperty(excel.ws, 2, 1, 2, dt.Columns.Count, 15, true, "黑体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                    excel.SetCellValue(excel.ws, 3, 1, "账单明细");//row3
                    excel.UniteCells(excel.ws, 3, 1, 3, dt.Columns.Count);
                    excel.SetCellProperty(excel.ws, 3, 1, 3, dt.Columns.Count, 15, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                    excel.SetCellValue(excel.ws, 4, 1, "致：" + this.cbBoxTarget.Text);//row4
                    excel.UniteCells(excel.ws, 4, 1, 4, dt.Columns.Count);
                    excel.SetCellProperty(excel.ws, 4, 1, 4, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlLeft, false);

                    for (int c = 0; c < dt.Columns.Count; c++)
                    {
                        excel.SetCellValue(excel.ws, 5, c + 1, dt.Columns[c].ColumnName);
                        excel.SetCellProperty(excel.ws, 5, c + 1, 5, c + 1, 13, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    }
                    excel.AddTable(dt, excel.ws, 6, 1);
                    //excel.SetRangeGrid(excel.ws, 5, 1, 5 + dt.Rows.Count, dt.Columns.Count, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);

                    //int rowTotal = 5 + dt.Rows.Count;
                    int rowTotal = 5 + dt.Rows.Count + 1;//rowTotal                    
                    excel.SetCellValue(excel.ws, rowTotal, 1, "总计");
                    excel.UniteCells(excel.ws, rowTotal, 1, rowTotal, dt.Columns.Count - 2);
                    excel.SetCellProperty(excel.ws, rowTotal, 1, rowTotal, dt.Columns.Count, 13, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                    ArrayList objs = new ArrayList { };
                    int totalColumnId = dt.Columns.Count - 2;
                    decimal total = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //int num = dt.Columns.Count - 6;

                        //for (int j = 6; j < dt.Columns.Count; j++)
                        //{
                        //    if (i == 0)
                        //        objs.Add(dt.Rows[i][j]);
                        //    else
                        //    {
                        //        if (!Common.IsDgvCellNull(dt.Rows[i][j]))
                        //        {
                        //            decimal m = Convert.ToDecimal(dt.Rows[i][j]);
                        //            //decimal value = Convert.ToDecimal(objs[j - 6]);
                        //            objs[j - 6] = Common.IsDgvCellNull(objs[j - 6]) ? m : Convert.ToDecimal(objs[j - 6]) + m;
                        //        }
                        //    }
                        //    if (i == dt.Rows.Count - 1)
                        //        excel.SetCellValue(excel.ws, rowTotal, j + 1, objs[j - 6]);
                        //}

                        if (!Common.IsDgvCellNull(dt.Rows[i][totalColumnId]))
                        {
                            total += Convert.ToDecimal(dt.Rows[i][totalColumnId]);
                        }
                    }
                    excel.SetCellValue(excel.ws, rowTotal, dt.Columns.Count - 1, total);

                    excel.SetRangeGrid(excel.ws, 5, 1, rowTotal, dt.Columns.Count, Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
                    excel.app.Visible = true;

                    if (this._FeeType == EnumCommon.FeeType.应收)
                    {
                        if (client != null)
                        {
                            excel.SetCellValue(excel.ws, rowTotal + 2, 1, client.bankAccount);
                            excel.UniteCells(excel.ws, rowTotal + 2, 1, rowTotal + 2, dt.Columns.Count / 2);
                            excel.SetCellProperty(excel.ws, rowTotal + 2, 1, rowTotal + 2, dt.Columns.Count / 2, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                        }
                        else
                        {
                            excel.SetCellValue(excel.ws, rowTotal + 2, 1, "开户行：中国工商银行-黄埔大道西支行");
                            excel.UniteCells(excel.ws, rowTotal + 2, 1, rowTotal + 2, dt.Columns.Count / 2);
                            excel.SetCellProperty(excel.ws, rowTotal + 2, 1, rowTotal + 2, dt.Columns.Count / 2, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                            excel.SetCellValue(excel.ws, rowTotal + 3, 1, "卡号：6222 0236 0204 0740 607");
                            excel.UniteCells(excel.ws, rowTotal + 3, 1, rowTotal + 3, dt.Columns.Count / 2);
                            excel.SetCellProperty(excel.ws, rowTotal + 3, 1, rowTotal + 3, dt.Columns.Count / 2, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                            excel.SetCellValue(excel.ws, rowTotal + 4, 1, "用户名：黄绍官");
                            excel.UniteCells(excel.ws, rowTotal + 4, 1, rowTotal + 4, dt.Columns.Count / 2);
                            excel.SetCellProperty(excel.ws, rowTotal + 4, 1, rowTotal + 4, dt.Columns.Count / 2, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                        }

                        excel.SetCellValue(excel.ws, rowTotal + 5, 1, "（如需要其它帐号，请看第二页的银行帐号）");
                        excel.UniteCells(excel.ws, rowTotal + 5, 1, rowTotal + 5, dt.Columns.Count / 2);
                        excel.SetCellProperty(excel.ws, rowTotal + 5, 1, rowTotal + 5, dt.Columns.Count / 2, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                        excel.SetCellValue(excel.ws, rowTotal + 2, dt.Columns.Count / 2 + 1, "请贵公司在收到账单后3个工作日内确认费用，否则当作默认处理。");
                        excel.UniteCells(excel.ws, rowTotal + 2, dt.Columns.Count / 2 + 1, rowTotal + 2 + 4, dt.Columns.Count);
                        excel.SetCellProperty(excel.ws, rowTotal + 2, dt.Columns.Count / 2 + 1, rowTotal + 2 + 4, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, true);


                        excel.SetCellValue(excel.ws, rowTotal + 7, 1, "如费用无误请尽快付款，谢谢！付款时，麻烦给予水单，方便销账！顺祝商祺！");
                        excel.UniteCells(excel.ws, rowTotal + 7, 1, rowTotal + 12, dt.Columns.Count / 2);
                        excel.SetCellProperty(excel.ws, rowTotal + 7, 1, rowTotal + 12, dt.Columns.Count / 2, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, true);


                        excel.SetCellValue(excel.ws, rowTotal + 7, dt.Columns.Count / 2 + 1, "制单人：黄先生/marco");
                        excel.UniteCells(excel.ws, rowTotal + 7, dt.Columns.Count / 2 + 1, rowTotal + 7, dt.Columns.Count);
                        excel.SetCellProperty(excel.ws, rowTotal + 7, dt.Columns.Count / 2 + 1, rowTotal + 7, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                        excel.SetCellValue(excel.ws, rowTotal + 8, dt.Columns.Count / 2 + 1, "电话：020-28945185");
                        excel.UniteCells(excel.ws, rowTotal + 8, dt.Columns.Count / 2 + 1, rowTotal + 8, dt.Columns.Count);
                        excel.SetCellProperty(excel.ws, rowTotal + 8, dt.Columns.Count / 2 + 1, rowTotal + 8, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                        excel.SetCellValue(excel.ws, rowTotal + 9, dt.Columns.Count / 2 + 1, "传真：020-89718757");
                        excel.UniteCells(excel.ws, rowTotal + 9, dt.Columns.Count / 2 + 1, rowTotal + 9, dt.Columns.Count);
                        excel.SetCellProperty(excel.ws, rowTotal + 9, dt.Columns.Count / 2 + 1, rowTotal + 9, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                        excel.SetCellValue(excel.ws, rowTotal + 11, dt.Columns.Count / 2 + 1, "广州市科洋货运代理财务部");
                        excel.UniteCells(excel.ws, rowTotal + 11, dt.Columns.Count / 2 + 1, rowTotal + 11, dt.Columns.Count);
                        excel.SetCellProperty(excel.ws, rowTotal + 11, dt.Columns.Count / 2 + 1, rowTotal + 11, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);

                        excel.SetCellValue(excel.ws, rowTotal + 12, dt.Columns.Count / 2 + 1, DateTime.Now.ToString("yyyy-MM-dd"));
                        excel.UniteCells(excel.ws, rowTotal + 12, dt.Columns.Count / 2 + 1, rowTotal + 12, dt.Columns.Count);
                        excel.SetCellProperty(excel.ws, rowTotal + 12, dt.Columns.Count / 2 + 1, rowTotal + 12, dt.Columns.Count, 12, true, "宋体", Microsoft.Office.Interop.Excel.Constants.xlColor3, Microsoft.Office.Interop.Excel.Constants.xlCenter, false);
                        //if (excel.SaveAs(@"d:\test.xls"))
                        //    MessageBox.Show("ok");
                        //else
                        //    MessageBox.Show("faile");
                        //excel.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
                        excel.ColumnsAutoFit(excel.ws, 1, 1, rowTotal + 12, dt.Columns.Count);

                    }
                    else
                        excel.ColumnsAutoFit(excel.ws, 1, 1, rowTotal, dt.Columns.Count);
                }
                //excel.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                excel.Dispose();
            }
            finally
            {

            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            ArrayList list = new ArrayList();
            for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
            {
                if (Convert.ToBoolean(this.dataGridView1.Rows[i].Cells[0].Value))
                    list.Add(i);
            }

            if (list.Count > 0)
            {
                for (int i = 0; i < this.dt.Rows.Count; i++)
                {
                    if (!list.Contains(i))
                        this.dt.Rows[i].Delete();
                }
                this.dt.AcceptChanges();

                if (ConfigurationManager.AppSettings["OrderCodeHeader"] == "YH")
                    printYHBillPre();
                else
                    printBill();
            }
            else
            {
                MessageBox.Show("请选择要导出的数据！");
                return;
            }

        }

        private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            Common.ShowDataGridViewRowNo(sender);
        }

        private void dataGridView1_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e)
        {
            Common.ShowDataGridViewRowNo(sender);
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            this.cbBoxTarget.SelectedIndex = -1;
        }

        private void rdBtnYS_CheckedChanged(object sender, EventArgs e)
        {
            bindTarget();
        }

        private void rdBtnYF_CheckedChanged(object sender, EventArgs e)
        {
            bindTarget();
        }

        private void dataGridView1_CurrentCellDirtyStateChanged(object sender, EventArgs e)
        {
            if (this.dataGridView1.IsCurrentCellDirty) //有未提交的更//改
            {
                this.dataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit);
            }
        }


        void printYHBillPre()
        {
            var comTitle = "广州市盈泓货运代理有限公司";
            var sDate = this.dtpSDate.Value.Date.ToString("yyyy-MM-dd");
            var eDate = this.dtpEDate.Value.Date.ToString("yyyy-MM-dd");

            var targetName = string.Empty;
            var contactMan = string.Empty;
            var contactTel = string.Empty;
            var fax = string.Empty;

            if (this._FeeType == EnumCommon.FeeType.应收)
            {
                var client = this.cbBoxTarget.SelectedItem as TB_Clients;
                targetName = client.fullName;
                contactMan = client.contactMan;
                contactTel = client.tel;
                fax = client.fax;
            }
            else
                targetName = (this.cbBoxTarget.SelectedItem as TB_Options).name;

            var opName = SysLoginUserInfo.UserName;
            var opTel = "020-66206216";
            var opFax = "020-82595753";
            yhBill(comTitle, sDate, eDate, targetName, contactMan, contactTel, fax, opName, opTel, opFax);
        }

        void yhBill(string comTitle, string sDate, string eDate, string targetName, string contactMan, string contactTel, string fax, string opName, string opTel, string opFax)
        {
            ExcelXmlWorkbook book = new ExcelXmlWorkbook();
            //string rtn = string.Empty;
            try
            {

                book.Properties.Author = "盈泓";
                Worksheet sheet = book[0];
                sheet.Name = "账单明细";

                // and this too...
                sheet.PrintOptions.Orientation = PageOrientation.Portrait;
                sheet.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4);


                XmlStyle ms = new XmlStyle();
                ms.Font.Size = 11;
                ms.Font.Name = "Swiss";
                ms.Alignment.Vertical = VerticalAlignment.Center;
                ms.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                ms.Alignment.WrapText = true;
                ms.Font.Bold = true;
                ms.Border.LineStyle = Borderline.Continuous;
                ms.Border.Weight = 2;
                ms.Border.Sides = BorderSides.All;

                XmlStyle s21 = new XmlStyle();
                s21.Font.Size = 12;
                s21.Font.Name = "宋体";
                s21.Alignment.Vertical = VerticalAlignment.Center;
                s21.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s21.Font.Bold = true;

                XmlStyle s22 = new XmlStyle();
                s22.Font.Size = 11;
                s22.Font.Name = "Swiss";
                s22.Alignment.Vertical = VerticalAlignment.Center;
                s22.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s22.Alignment.WrapText = true;
                s22.Font.Bold = true;
                s22.Border.LineStyle = Borderline.Continuous;
                s22.Border.Weight = 2;
                s22.Border.Sides = BorderSides.All;

                XmlStyle s23 = new XmlStyle();
                s23.Font.Size = 11;
                s23.Font.Name = "Swiss";
                s23.Alignment.Vertical = VerticalAlignment.Center;
                s23.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s23.Alignment.WrapText = true;
                s23.Font.Bold = true;
                s23.Border.LineStyle = Borderline.Continuous;
                s23.Border.Weight = 2;
                s23.Border.Sides = BorderSides.Left;
                s23.Border.Sides = BorderSides.Right;
                s23.Border.Sides = BorderSides.Top;

                XmlStyle s24 = new XmlStyle();
                s24.Font.Size = 11;
                s24.Font.Name = "Swiss";
                s24.Alignment.Vertical = VerticalAlignment.Center;
                s24.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s24.Alignment.WrapText = true;
                s24.Font.Bold = true;
                s24.Border.LineStyle = Borderline.Continuous;
                s24.Border.Weight = 2;
                s24.Border.Sides = BorderSides.Left;
                s24.Border.Sides = BorderSides.Right;
                s24.Border.Sides = BorderSides.Bottom;

                XmlStyle s25 = new XmlStyle();
                s25.Font.Size = 11;
                s25.Font.Name = "Swiss";
                s24.Alignment.Vertical = VerticalAlignment.Center;
                s24.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s24.Alignment.WrapText = true;
                s24.Font.Bold = true;
                s24.Border.LineStyle = Borderline.Continuous;
                s24.Border.Weight = 2;
                s24.Border.Sides = BorderSides.Left;
                s24.Border.Sides = BorderSides.Right;

                XmlStyle s26 = new XmlStyle();
                s26.Font.Size = 11;
                s26.Font.Name = "Swiss";
                s26.Alignment.Vertical = VerticalAlignment.Center;
                s26.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s26.Alignment.WrapText = true;
                s26.Font.Bold = true;

                XmlStyle s27 = new XmlStyle();
                s27.Font.Size = 11;
                s27.Font.Name = "宋体";
                s27.Alignment.Vertical = VerticalAlignment.Center;
                s27.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s27.Alignment.WrapText = true;
                s27.Font.Bold = true;
                s27.Border.LineStyle = Borderline.Continuous;
                s27.Border.Weight = 2;
                s27.Border.Sides = BorderSides.Left;
                s27.Border.Sides = BorderSides.Right;
                s27.Border.Sides = BorderSides.Top;

                XmlStyle s28 = new XmlStyle();
                s28.Font.Size = 11;
                s28.Font.Name = "宋体";
                s28.Alignment.Vertical = VerticalAlignment.Center;
                s28.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s28.Alignment.WrapText = true;
                s28.Font.Bold = true;
                s28.Border.LineStyle = Borderline.Continuous;
                s28.Border.Weight = 2;
                s28.Border.Sides = BorderSides.Left;
                s28.Border.Sides = BorderSides.Right;
                s28.Border.Sides = BorderSides.Bottom;

                XmlStyle s29 = new XmlStyle();
                s29.Font.Size = 11;
                s29.Font.Name = "宋体";
                s29.Alignment.Vertical = VerticalAlignment.Center;
                s29.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s29.Alignment.WrapText = true;
                s29.Font.Bold = true;
                s29.Border.LineStyle = Borderline.Continuous;
                s29.Border.Weight = 2;
                s29.Border.Sides = BorderSides.Left;
                s29.Border.Sides = BorderSides.Right;

                XmlStyle s44 = new XmlStyle();
                s44.Font.Size = 12;
                s44.Font.Name = "宋体";
                s44.Alignment.Vertical = VerticalAlignment.Center;
                s44.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                s44.Alignment.WrapText = true;
                s44.Font.Bold = true;

                XmlStyle s46 = new XmlStyle();
                s46.Font.Size = 22;
                s46.Font.Name = "华文新魏";
                s46.Alignment.Vertical = VerticalAlignment.Center;
                s46.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                s46.Alignment.WrapText = true;
                s46.Font.Bold = true;

                XmlStyle s47 = new XmlStyle();
                s47.Font.Size = 12;
                s47.Font.Name = "宋体";
                s47.Alignment.Vertical = VerticalAlignment.Center;
                s47.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Right;
                s47.Alignment.WrapText = true;
                s47.Font.Bold = true;

                XmlStyle s48 = new XmlStyle();
                s48.Font.Size = 24;
                s48.Font.Name = "宋体";
                s48.Alignment.Vertical = VerticalAlignment.Center;
                s48.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s48.Font.Bold = true;

                XmlStyle s49 = new XmlStyle();
                s49.Font.Size = 18;
                s49.Font.Name = "宋体";
                s49.Alignment.Vertical = VerticalAlignment.Center;
                s49.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s49.Font.Bold = true;

                XmlStyle s50 = new XmlStyle();
                s50.Font.Size = 14;
                s50.Font.Name = "Times New Roman";
                s50.Alignment.Vertical = VerticalAlignment.Center;
                s50.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center;
                s50.Font.Bold = true;
                s50.Border.LineStyle = Borderline.Double;
                s50.Border.Sides = BorderSides.Bottom;
                s50.Border.Weight = 3;

                XmlStyle s57 = new XmlStyle();
                s57.Font.Size = 12;
                s57.Font.Name = "宋体";
                s57.Alignment.Vertical = VerticalAlignment.Center;
                s57.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                s57.Font.Bold = true;

                XmlStyle s58 = new XmlStyle();
                s58.Font.Size = 12;
                s58.Font.Name = "宋体";
                s58.Alignment.WrapText = true;
                s58.Alignment.Vertical = VerticalAlignment.Center;
                s58.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                s58.Font.Bold = true;

                XmlStyle s59 = new XmlStyle();
                s59.Font.Size = 12;
                s59.Font.Name = "宋体";
                s59.Alignment.Vertical = VerticalAlignment.Center;
                s59.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left;
                s59.Font.Bold = true;
                s59.Border.LineStyle = Borderline.Continuous;
                s59.Border.Sides = BorderSides.Bottom;
                s59.Border.Weight = 2;

                int currentRow = 0;
                sheet[0, currentRow].Value = comTitle;
                sheet[0, currentRow].Style = s48;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "GUANGZHOU YINGHONG LOGISTICS CO.,LTD";
                sheet[0, currentRow].Style = s50;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = sDate + "至" + eDate + "帐单明细";
                sheet[0, currentRow].Style = s49;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "致：" + targetName + "  联系人:" + contactMan + " 电话：" + contactTel + "  传真：" + fax;
                sheet[0, currentRow].Style = s59;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "我司单号";
                sheet[0, currentRow].Style = s22;
                sheet[1, currentRow].Value = "提单号";
                sheet[1, currentRow].Style = s22;
                sheet[2, currentRow].Value = "箱号/箱型";
                sheet[2, currentRow].Style = s22;
                sheet[3, currentRow].Value = "明细";
                sheet[3, currentRow].Style = s22;
                sheet[4, currentRow].Value = "小计";
                sheet[4, currentRow].Style = s22;
                if (this.dt != null)
                {
                    if (this.dt.Rows.Count > 0)
                    {
                        int orderId = this.dt.Rows[0].Field<int>("orderId");
                        TB_Order order = this.BaseDataProxy.GetOrder(orderId);
                        int merginRows = 0;
                        foreach (DataRow dr in this.dt.Rows)
                        {
                            currentRow++;
                            string[] ary = dr.Field<string>("费用").Split('\n');

                            sheet[0, currentRow].Value = order.code;
                            sheet[0, currentRow].Style = s22;
                            sheet[1, currentRow].Value = dr.Field<string>("订舱号");
                            sheet[1, currentRow].Style = s22;
                            sheet[2, currentRow].Value = dr.Field<string>("箱号") + "/" + dr.Field<string>("箱型");
                            sheet[2, currentRow].Style = s22;
                            sheet[4, currentRow].Value = dr.Field<decimal>("总额");
                            sheet[4, currentRow].Style = s22;

                            if (ary.Length > 1)
                            {
                                var margin = currentRow + ary.Length - 1;
                                new Range(sheet[0, currentRow], sheet[0, margin]).Merge();
                                new Range(sheet[1, currentRow], sheet[1, margin]).Merge();
                                new Range(sheet[2, currentRow], sheet[2, margin]).Merge();
                                new Range(sheet[4, currentRow], sheet[4, margin]).Merge();
                                merginRows += ary.Length - 1;
                            }

                            for (var i = 0; i < ary.Length; i++)
                            {
                                var r = currentRow + i;
                                sheet[3, r].Value = ary[i];
                                sheet[3, r].Style = ms;
                            }

                        }

                        currentRow += merginRows;
                        currentRow++;
                        sheet[0, currentRow].Value = "合计：";
                        sheet[0, currentRow].Style = s22;
                        new Range(sheet[0, currentRow], sheet[3, currentRow]).Merge();
                        var dts = currentRow - dt.Rows.Count - merginRows;
                        var dte = currentRow - 1;
                        sheet[4, currentRow].Value = FormulaHelper.Formula("sum", new Range(sheet[4, dts], sheet[4, dte]), cell => cell.Style == s22);
                        sheet[4, currentRow].Style = s22;

                    }
                }

                currentRow++;
                sheet[0, currentRow].Value = "";
                sheet[0, currentRow].Style = s26;

                currentRow++;
                sheet[0, currentRow].Value = "备注：1、请收到此账单三天内签章回传至020-82595753，三天内未回传视同默认";
                sheet[0, currentRow].Style = s58;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "      2、如需开发票请注明抬头、币种、联系人、电话与邮寄地址";
                sheet[0, currentRow].Style = s58;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "****付款需付入我司以下指定帐户，并传水单";
                sheet[0, currentRow].Style = s57;
                new Range(sheet[0, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "开户行：工商银行——广州黄埔支行怡港支行";
                sheet[0, currentRow].Style = s44;
                new Range(sheet[0, currentRow], sheet[2, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "账  号：3602 0885 0920 0043 218";
                sheet[0, currentRow].Style = s44;
                new Range(sheet[0, currentRow], sheet[2, currentRow]).Merge();

                currentRow++;
                sheet[0, currentRow].Value = "用户名：广州市盈泓货运代理有限公司";
                sheet[0, currentRow].Style = s44;
                new Range(sheet[0, currentRow], sheet[2, currentRow]).Merge();

                sheet[3, currentRow].Value = "";
                sheet[3, currentRow].Style = s44;
                new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[3, currentRow].Value = "制单人：" + opName;
                sheet[3, currentRow].Style = s47;
                new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[3, currentRow].Value = "电话：" + opTel + "                           传真：" + opFax;
                sheet[3, currentRow].Style = s47;
                new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                currentRow++;
                sheet[3, currentRow].Value = "传真：" + opFax;
                sheet[3, currentRow].Style = s47;
                new Range(sheet[3, currentRow], sheet[4, currentRow]).Merge();

                sheet.Columns(0).Width = 87.75;
                sheet.Columns(1).Width = 114;
                sheet.Columns(2).Width = 129;
                sheet.Columns(3).Width = 137.25;
                sheet.Columns(4).Width = 80.25;

                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter = "Excel文件|*.xls|XML数据|*.xml";
                saveFileDialog.RestoreDirectory = true;
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                    book.Export(saveFileDialog.FileName);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


    }
}
